/*
 * Created on May 31, 2004
 * by Mihai Postelnicu
 * email mihai@code.ro
 *
 *
 */
package ro.gateway.aida.search;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import ro.gateway.aida.db.DBWrapper;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.AIDASector;
import ro.gateway.aida.obj.ActivityStatus;
import ro.gateway.aida.obj.CRegion;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.County;
import ro.gateway.aida.obj.Organization;
import ro.gateway.aida.obj.Person;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.obj.Town;
import ro.gateway.aida.obj.Village;
import ro.gateway.aida.obj.db.AIDALocationDB;
import ro.gateway.aida.obj.db.AIDASectorPersMan;
import ro.gateway.aida.obj.db.ActivityStatusDB;
import ro.gateway.aida.obj.db.CountyDB;
import ro.gateway.aida.obj.db.OrganizationDB;
import ro.gateway.aida.obj.db.PersonDB;
import ro.gateway.aida.obj.db.RegionDB;
import ro.gateway.aida.obj.db.SearchDB;
import ro.gateway.aida.obj.db.TownDB;
import ro.gateway.aida.obj.db.VillageDB;
import ro.gateway.aida.struts.MultiTilesAction;
import ro.gateway.aida.struts.form.ProjectSearchForm;
import ro.gateway.aida.usr.User;
import ro.gateway.aida.usr.UserUtils;
import ro.gateway.aida.utils.LocalisedList;
import ro.gateway.aida.utils.PageToken;
import ro.gateway.aida.utils.Utils;
import ro.xblue.translator.TranslatorUtils;

/**
 * @author Mihai Postelnicu email mihai@code.ro
 */
public class ProjectSearchAction extends MultiTilesAction {

	private DBWrapper db;
	/*
	 * (non-Javadoc)
	 * 
	 * @see ro.gateway.aida.struts.MultiTilesAction#modePrepare(org.apache.struts.action.ActionMapping,
	 *      org.apache.struts.action.ActionForm,
	 *      javax.servlet.http.HttpServletRequest,
	 *      javax.servlet.http.HttpServletResponse)
	 */
	public ActionForward modePrepare(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		User user = UserUtils.getLogged(request, response);

		return modeSelect(mapping, form, request, response);
	}

	public void grabObjects(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		String lang = TranslatorUtils.getCurrentLanguage(request,
				getServlet().getServletContext()).getLanguage();
		ProjectSearchForm f = (ProjectSearchForm) form;
		if (f.getSector() != -1) {
			AIDASector as = AIDASectorPersMan.getManager(token).getById(
					f.getSector());
			as.setViewLang(lang);
			request.setAttribute("Sector", as);
		}
		if (f.getContractingOrg() != -1) {
			Organization o = OrganizationDB.getManager(token).getByID(
					f.getContractingOrg());
			o.setViewLang(lang);
			request.setAttribute("ContractingOrg", o);
		}
		if (f.getBeneficiaryOrg() != -1) {
			Organization o = OrganizationDB.getManager(token).getByID(
					f.getBeneficiaryOrg());
			o.setViewLang(lang);
			request.setAttribute("BeneficiaryOrg", o);
		}
		if (f.getFundingOrg() != -1) {
			Organization o = OrganizationDB.getManager(token).getByID(
					f.getFundingOrg());
			o.setViewLang(lang);
			request.setAttribute("FundingOrg", o);
		}
		if (f.getDonorOrg() != -1) {
			Organization o = OrganizationDB.getManager(token).getByID(
					f.getDonorOrg());
			o.setViewLang(lang);
			request.setAttribute("DonorOrg", o);
		}
		if (f.getInvolvedPerson() != -1) {
			Person p = PersonDB.getManager(token)
					.getByID(f.getInvolvedPerson());
			request.setAttribute("Person", p);
		}

		if (f.getLoc_countryiso3() != null || f.getLoc_countyId() != -1
				|| f.getLoc_regionId() != -1 || f.getLoc_townId() != -1
				|| f.getLoc_cregionId() != -1 || f.getLoc_villageId() != -1) {

			int jid = f.getLoc_countyId();
			String country_iso3 = f.getLoc_countryiso3();
			int cregion_id = f.getLoc_cregionId();
			int rid = f.getLoc_regionId();
			int oid = f.getLoc_townId();
			int lid = f.getLoc_villageId();

			AIDALocation location = new AIDALocation();
			try {
				while (true) {
					if (lid != -1) {
						Village loc = VillageDB.getManager(token).getById(lid);
						location.setLocalitate(loc);
						if (loc != null && loc.getSirsup_id() != -1) {
							oid = loc.getSirsup_id();
						} else {
							location = null;
							break;
						}
					}
					if (oid != -1) {
						Town town = TownDB.getManager(token).getById(oid);
						location.setOras(town);
						if (town != null && town.getJudet_id() != -1) {
							jid = town.getJudet_id();
						} else {
							location = null;
							break;
						}
					}
					if (jid != -1) {
						County county = CountyDB.getManager(token).getById(jid);
						location.setJudet(county);
						if (county != null && county.getRegiune_id() != -1) {
							rid = county.getRegiune_id();
						} else {
							location = null;
							break;
						}
					}
					if (rid != -1) {
						Region region = RegionDB.getManager(token).getById(rid);
						location.setRegion(region);
						if (region != null && region.getCountryIso3() != null) {
							country_iso3 = region.getCountryIso3();
						} else {
							location = null;
							break;
						}
					}
					if (country_iso3 != null) {
						Country country = AIDALocationDB.getManager(token)
								.getCountryByISO3(country_iso3);
						location.setCountry(country);
						if (country != null && country.getCregion_id() != -1) {
							cregion_id = country.getCregion_id();
						} else {
							location = null;
							break;
						}
					}
					if (cregion_id != -1) {
						CRegion cregion = AIDALocationDB.getManager(token)
								.getCRegionById(cregion_id);
						//                              System.out.println( "cregion:" + cregion );
						location.setCregion(cregion);
						if (cregion == null) {
							location = null;
							break;
						}
					}
					break;
				}
			} catch (SQLException sqlEx) {
				sqlEx.printStackTrace();
			}

			if (location != null) {
				request.setAttribute("Location", location);

			}

		}

	}

	public ActionForward modeSort(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		int sidx = Integer.parseInt(request.getParameter("sortby"));
		HttpSession session = request.getSession();
		List results = (List) session.getAttribute("results");
		Iterator i = results.iterator();
		while (i.hasNext()) {
			ResultRow r = (ResultRow) i.next();
			r.setSortIdx(sidx);
		}
		Collections.sort(results);
		return modeView(mapping, form, request, response);
	}

	public ActionForward modeReportTypeSearch(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ProjectSearchForm f = (ProjectSearchForm) form;
		String country_iso3=request.getParameter("ccode");
		String sector_code=request.getParameter("scode");
		String status_code=request.getParameter("statcode");
		String ssrcorgid=request.getParameter("srcorgid");
		String fundingOrgId=request.getParameter("fundingOrgId");
		String regionCode=request.getParameter("regionCode");
		if(regionCode!=null) f.setLoc_regionId(Integer.parseInt(regionCode));
		if(ssrcorgid!=null) f.setInfoSourceOrg(Long.parseLong(ssrcorgid));
		if(fundingOrgId!=null) f.setFundingOrg(Long.parseLong(fundingOrgId));
		if(country_iso3!=null) f.setLoc_countryiso3(country_iso3);
		if(sector_code!=null) f.setSector(Integer.parseInt(sector_code));
		if(status_code!=null) f.setStatus(Integer.parseInt(status_code));
		return modeSearchForm(mapping, form, request, response);
	}

	
	public ActionForward modeViewAll(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		
		return modeSearchForm(mapping, form, request, response);
	}

	public ActionForward modeKeySearch(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ProjectSearchForm f = (ProjectSearchForm) form;
		f.setKeywords(request.getParameter("stext"));
		
		return modeSearchForm(mapping, form, request, response);
	}

	
	public ActionForward modeTitleSearch(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ProjectSearchForm f = (ProjectSearchForm) form;
		f.setTitle(request.getParameter("stext"));
		
		return modeSearchForm(mapping, form, request, response);
	}

	
	public ActionForward modeView(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		HttpSession session = request.getSession();
		String lang = TranslatorUtils.getCurrentLanguage(request, sc)
				.getLanguage();
		Utils.setBreadCrumbs(request, "bc.prjsearch.results");
		LocalisedList results = (LocalisedList) session.getAttribute("results");
		if (!lang.equals(results.getViewLang())) {
			results.setViewLang(lang);
			Collections.sort(results);
		}

		PageToken.tokenizeList(request, sc, results, "page_results");

		return mapping.findForward("list");
	}

	public ActionForward modeClear(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ProjectSearchForm f = (ProjectSearchForm) form;
		f.reset(mapping, request);
		return modeForm(mapping, form, request, response);
	}
	
	public ActionForward modeExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response) 
	throws Exception {
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition",
           "inline; filename=LPDExport.xls");

		try {
			WritableWorkbook workbook = Workbook.createWorkbook(response
					.getOutputStream());
			WritableSheet sheet = workbook.createSheet(
					"LPD Search Results", 0);
			HttpSession session = request.getSession();
			List results = (List) session.getAttribute("results");
			
				
			//determining the max 
			
			//writing titles
			int xadd=0;
			ResultRow t=(ResultRow)results.get(0);
			Iterator i=t.iterator();
			
			while (i.hasNext()) {
				ResultCell c = (ResultCell) i.next();
				xadd+=c.appendXlsCells(sheet,xadd,0,true);
			}
		
				
			
			
			/*sheet.addCell(new Label(0, 0, "Project Title", times12format));
			sheet.addCell(new Label(1, 0, "Country", times12format));
			sheet.addCell(new Label(2, 0, "Sector", times12format));
			*/
			
			for(int k=0;k<results.size();k++) {
				xadd=0;
				ResultRow r=(ResultRow)results.get(k);				
				i=r.iterator();
				while (i.hasNext()) {
					ResultCell c = (ResultCell) i.next();
					xadd+=c.appendXlsCells(sheet,xadd,k+1,false);
				}				
			}
			workbook.write();
			workbook.close();
			
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ActionForward modeSelect(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		if (request.getParameter("newsearch") != null)
			return modeForm(mapping, form, request, response);
		if (request.getParameter("search") != null)
			return modeSearchForm(mapping, form, request, response);
		if (request.getParameter("clear") != null)
			return modeClear(mapping, form, request, response);
		if (request.getParameter("viewall") != null)
			return modeViewAll(mapping, form, request, response);
		if (request.getParameter("excel") != null)
			return modeExcel(mapping, form, request, response);
		if (request.getParameter("report") != null)
			return modeReportTypeSearch(mapping, form, request, response);
		
		
		
		if (request.getParameter("keywordsearch") != null)
			return modeKeySearch(mapping, form, request, response);
		
		if (request.getParameter("titlesearch") != null)
			return modeTitleSearch(mapping, form, request, response);
		
		
		if (request.getParameter("sortby") != null)
			return modeSort(mapping, form, request, response);
		if (request.getParameter("extern_modifier") != null
				&& request.getParameter("extern_modifier") != "x")
			return modeForm(mapping, form, request, response);
		
		HttpSession session = request.getSession();
		if(session.getAttribute("results")!=null)
		return modeView(mapping, form, request, response);
		else
		return modeViewAll(mapping, form, request, response);
	}

	public ActionForward modeForm(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		Utils.setBreadCrumbs(request, "bc.prjsearch.form");
		HttpSession session = request.getSession();
		ProjectSearchForm f = (ProjectSearchForm) form;
		db = DBWrapper.getManager(token);
		ResultsFormatDB rfdb = ResultsFormatDB.getManager(token);
		User user = UserUtils.getLogged(request, response);
		long uid = -1;
		if (user != null)
			uid = user.getId();
			session.setAttribute(ResultsFormat.LIST, rfdb.getForUser(uid,true));

		if (session.getAttribute(ActivityStatus.LIST) == null)
			session.setAttribute(ActivityStatus.LIST, ActivityStatusDB
					.getManager(token).getHarmonized());

		String extern_modifier = request.getParameter("extern_modifier");

		if ("donor".equals(extern_modifier)) {
			List l = (List) session.getAttribute("SelectedOrgs");
			if ((l != null) && (l.size() != 0))
				f.setDonorOrg(((Organization) l.get(0)).getId());
			else
				f.setDonorOrg(-1);
		}
		if ("beneficiary".equals(extern_modifier)) {
			List l = (List) session.getAttribute("SelectedOrgs");
			if ((l != null) && (l.size() != 0))
				f.setBeneficiaryOrg(((Organization) l.get(0)).getId());
			else
				f.setBeneficiaryOrg(-1);
		}
		if ("funding".equals(extern_modifier)) {
			List l = (List) session.getAttribute("SelectedOrgs");
			if ((l != null) && (l.size() != 0))
				f.setFundingOrg(((Organization) l.get(0)).getId());
			else
				f.setFundingOrg(-1);
		}
		if ("contracting".equals(extern_modifier)) {
			List l = (List) session.getAttribute("SelectedOrgs");
			if ((l != null) && (l.size() != 0))
				f.setContractingOrg(((Organization) l.get(0)).getId());
			else
				f.setContractingOrg(-1);
		}
		if ("sector".equals(extern_modifier)) {
			AIDASector s = AIDASectorPersMan.getManager(token).getById(
					Integer.parseInt(request.getParameter("sector_id")));
			if (s != null)
				f.setSector(s.getRo_aida_code());
			else
				f.setSector(-1);
		}
		if ("person".equals(extern_modifier)) {
			Person p = (Person) session.getAttribute(Person.SELECTED);
			if (p != null)
				f.setInvolvedPerson(p.getId());
			else
				f.setInvolvedPerson(-1);
		}

		if ("location".equals(extern_modifier)) {
			/*
			 * int rid = Utils.getInt(request, "rid", -1); int jid =
			 * Utils.getInt(request, "jid", -1); int oid = Utils.getInt(request,
			 * "oid", -1); int lid = Utils.getInt(request, "lid", -1); String
			 * country_iso3 = Utils.getValidTrimedString(request, "country",
			 * null); int cregion_id = Utils.getInt(request, "cregion", -1);
			 * f.setLoc_countryiso3(country_iso3); f.setLoc_countyId(jid);
			 * f.setLoc_cregionId(cregion_id); f.setLoc_regionId(rid);
			 * f.setLoc_townId(oid); f.setLoc_villageId(lid);
			 */
		}

		grabObjects(mapping, form, request, response);
		return mapping.getInputForward();
	}

	public ActionForward modeSearchForm(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		ProjectSearchForm f = (ProjectSearchForm) form;
		HttpSession session = request.getSession();
		
		SearchDB sdb = SearchDB.getManager(token);
		sdb.setDonorOrg((int) f.getDonorOrg());
		sdb.setBeneficiaryOrg((int) f.getBeneficiaryOrg());
		sdb.setContractingOrg((int) f.getContractingOrg());
		sdb.setFundingOrg((int) f.getFundingOrg());
		sdb.setStatus((int) f.getStatus());
		sdb.setInfoSourceOrg((int)f.getInfoSourceOrg());
		if (f.getSector() != -1)
			sdb.setSectors(Integer.toString(f.getSector()));
		if (!f.getTitle().equals(""))
			sdb.setTitle(f.getTitle());
		if (!f.getKeywords().equals(""))
			sdb.setKeywords(f.getKeywords());
		if (!f.getDescription().equals(""))
			sdb.setDescription(f.getDescription());
		sdb.setInvolvedPerson((int) f.getInvolvedPerson());

		ResultsFormatDB rfdb = ResultsFormatDB.getManager(token);
		long uid=-1;
		ResultsFormat rf=rfdb.get(f.getFormatId());
		//locations
		sdb.setLoc_countryiso3(f.getLoc_countryiso3());
		sdb.setLoc_countyId(f.getLoc_countyId());
		sdb.setLoc_cregionId(f.getLoc_cregionId());
		sdb.setLoc_regionId(f.getLoc_regionId());
		sdb.setLoc_townId(f.getLoc_townId());
		sdb.setLoc_villageId(f.getLoc_villageId());

		if (f.getSector() != -1)
			sdb.setSectors(Integer.toString(f.getSector()));
		sdb.setInvolvedPerson((int) f.getInvolvedPerson());

		String projectq = sdb.createSearchQuery();
		session.setAttribute("search_query_for_charts", projectq);

		SearchColumnDB scdb = SearchColumnDB.getManager(token);
		ArrayList columns = new ArrayList();
		rf.initTokenizer();
		while(true) {
			String c=rf.getNextColumn();
			if(c==null) break;
			columns.add(c);
		}
/*		
		fake_columns.add(ResultsFormat.TITLE_COLUMN);
		fake_columns.add(ResultsFormat.PROJECT_ID_COLUMN);
		//fake_columns.add(ResultsFormat.PROPOSED_APPROVAL_DATE_COLUMN);
		//fake_columns.add(ResultsFormat.PROPOSED_STARTING_DATE_COLUMN);
		//fake_columns.add(ResultsFormat.ACTUAL_APPROVAL_DATE_COLUMN);
		//fake_columns.add(ResultsFormat.ACTUAL_STARTING_DATE_COLUMN);
		fake_columns.add(ResultsFormat.SECTOR_COLUMN);
		//fake_columns.add(ResultsFormat.FUNDING_ORG_COLUMN);
		//fake_columns.add(ResultsFormat.FUNDING_AMOUNT_COLUMN);
		fake_columns.add(ResultsFormat.FUNDING_AMOUNT_ORG_COLUMN);
		fake_columns.add(ResultsFormat.FUNDING_AMOUNT_ORG_DISB_COLUMN);
		fake_columns.add(ResultsFormat.FUNDING_AMOUNT_ORG_COMM_COLUMN);
		fake_columns.add(ResultsFormat.CREGION_COLUMN);
*/
		List l = scdb.createSearchResults(columns, projectq);
		session.setAttribute("results", l);
		
		return modeView(mapping, form, request, response);
	}

}